
"""wav_files表对应的实体类"""
class WavFile:

    def __init__(self, id=None, user_english_name=None, user_chinese_name=None,
                 user_identity=None, audio_type=None, file_data=None, upload_time=None):
        self.id = id
        self.user_english_name = user_english_name
        self.user_chinese_name = user_chinese_name
        self.user_identity = user_identity
        self.audio_type = audio_type
        self.file_data = file_data
        self.upload_time = upload_time


"""wav_files表的SQL映射操作"""
class WavMapper:

    def __init__(self, db_cursor):
        self.cursor = db_cursor

    """插入一条WAV文件记录"""
    def insert(self, wav_file):
        sql = """
        INSERT INTO wav_files 
        (user_english_name, user_chinese_name, user_identity, audio_type, file_data, upload_time) 
        VALUES (%s, %s, %s, %s, %s, %s)
        """
        params = (
            wav_file.user_english_name,
            wav_file.user_chinese_name,
            wav_file.user_identity,
            wav_file.audio_type,
            wav_file.file_data,
            wav_file.upload_time
        )
        self.cursor.execute(sql, params)
        return self.cursor.lastrowid  # 返回自增ID

    """根据用户英文名查询WAV文件记录"""
    def select_by_user_english_name(self, user_english_name):
        sql = "SELECT * FROM wav_files WHERE user_english_name = %s"
        self.cursor.execute(sql, (user_english_name,))
        results = self.cursor.fetchall()
        wav_files = []
        for result in results:
            wav_files.append(WavFile(
                id=result[0],
                user_english_name=result[1],
                user_chinese_name=result[2],
                user_identity=result[3],
                audio_type=result[4],
                file_data=result[5],
                upload_time=result[6]
            ))
        return wav_files

    """根据用户英文名删除WAV文件记录"""
    def delete_by_user_english_name(self, user_english_name):
        sql = "DELETE FROM wav_files WHERE user_english_name = %s"
        self.cursor.execute(sql, (user_english_name,))
        return self.cursor.rowcount

    """根据用户英文名获取身份信息"""
    def get_user_identity_by_user_english_name(self, user_english_name):
        sql = "SELECT user_identity FROM wav_files WHERE user_english_name = %s"
        self.cursor.execute(sql, (user_english_name,))
        result = self.cursor.fetchone()
        return result[0] if result else None



    """检查用户英文名是否已存在"""
    def check_user_english_name_exists(self, user_english_name):
        sql = "SELECT COUNT(*) FROM wav_files WHERE user_english_name = %s"
        self.cursor.execute(sql, (user_english_name,))
        result = self.cursor.fetchone()
        return result[0] > 0
